﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace H5.FIO {

    /// <summary>
    /// 提供操作数据库的工具函数。
    /// </summary>
    public static class DbHelper {

        #region 底层数据库操作

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>受影响的行数。</returns>
        public static int executeSqlNonQuery(string sql, params object[] args) {
            using (var conn = new SqlConnection(ConfigManager.connectionString)) {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                addParameters(cmd, args);
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <returns>受影响的行数。</returns>
        public static int executeSqlNonQuery(string sql) {
            return executeSqlNonQuery(sql, null);
        }

        /// <summary>
        /// 执行 SQL 语句并返回结果集中的第一行的第一列。忽略其它行或列。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>结果集中第一行的第一列；如果结果集为空，则为 null。返回的最大字符数为 2033 个字符。</returns>
        public static object executeSqlScalar(string sql, params object[] args) {
            using (var conn = new SqlConnection(ConfigManager.connectionString)) {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                addParameters(cmd, args);
                return cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// 执行 SQL 语句并返回结果集中的第一行的第一列。忽略其它行或列。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <returns>结果集中第一行的第一列；如果结果集为空，则为 null。返回的最大字符数为 2033 个字符。</returns>
        public static object executeSqlScalar(string sql) {
            return executeSqlScalar(sql, null);
        }

        /// <summary>
        /// 执行 SQL 语句并返回一个数据读取器。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个数据读取器。需要释放其资源以关闭连接。</returns>
        public static SqlDataReader executeSqlReader(string sql, params object[] args) {
            var conn = new SqlConnection(ConfigManager.connectionString);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            addParameters(cmd, args);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行 SQL 语句并返回一个数据读取器。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <returns>一个数据读取器。需要释放其资源以关闭连接。</returns>
        public static SqlDataReader executeSqlReader(string sql) {
            return executeSqlReader(sql, null);
        }

        /// <summary>
        /// 执行数据库语句返回一个用于遍历所有行数据的迭代器。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static IEnumerable<SqlDataReader> executeSqlRows(string sql, params object[] args) {
            var conn = new SqlConnection(ConfigManager.connectionString);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            addParameters(cmd, args);
            return new RowEnumerator(cmd.ExecuteReader(CommandBehavior.CloseConnection));
        }

        /// <summary>
        /// 执行数据库语句返回一个用于遍历所有行数据的迭代器。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static IEnumerable<SqlDataReader> executeSqlRows(string sql) {
            return executeSqlRows(sql, null);
        }

        private struct RowEnumerator : IEnumerator<SqlDataReader>, IEnumerable<SqlDataReader> {

            private readonly SqlDataReader _reader;

            public RowEnumerator(SqlDataReader reader) {
                _reader = reader;
            }

            /// <summary>
            /// 获取集合中位于枚举数当前位置的元素。
            /// </summary>
            /// <returns>
            /// 集合中位于枚举数当前位置的元素。
            /// </returns>
            public SqlDataReader Current {
                get {
                    return _reader;
                }
            }

            /// <summary>
            /// 执行与释放或重置非托管资源相关的应用程序定义的任务。
            /// </summary>
            /// <filterpriority>2</filterpriority>
            public void Dispose() {
                _reader.Dispose();
            }

            /// <summary>
            /// 获取集合中的当前元素。
            /// </summary>
            /// <returns>
            /// 集合中的当前元素。
            /// </returns>
            /// <filterpriority>2</filterpriority>
            object System.Collections.IEnumerator.Current {
                get {
                    return _reader;
                }
            }

            /// <summary>
            /// 将枚举数推进到集合的下一个元素。
            /// </summary>
            /// <returns>
            /// 如果枚举数成功地推进到下一个元素，则为 true；如果枚举数越过集合的结尾，则为 false。
            /// </returns>
            /// <exception cref="T:System.InvalidOperationException">在创建了枚举数后集合被修改了。</exception><filterpriority>2</filterpriority>
            public bool MoveNext() {
                return _reader.Read();
            }

            /// <summary>
            /// 将枚举数设置为其初始位置，该位置位于集合中第一个元素之前。
            /// </summary>
            /// <exception cref="T:System.InvalidOperationException">在创建了枚举数后集合被修改了。</exception><filterpriority>2</filterpriority>
            public void Reset() {
                throw new InvalidOperationException("无法重置 SqlDataReader，请重新查询");
            }

            /// <summary>
            /// 返回一个循环访问集合的枚举器。
            /// </summary>
            /// <returns>
            /// 可用于循环访问集合的 <see cref="T:System.Collections.Generic.IEnumerator`1"/>。
            /// </returns>
            /// <filterpriority>1</filterpriority>
            public IEnumerator<SqlDataReader> GetEnumerator() {
                return this;
            }

            /// <summary>
            /// 返回一个循环访问集合的枚举器。
            /// </summary>
            /// <returns>
            /// 可用于循环访问集合的 <see cref="T:System.Collections.IEnumerator"/> 对象。
            /// </returns>
            /// <filterpriority>2</filterpriority>
            System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() {
                return this;
            }
        }

        /// <summary>
        /// 执行数据库语句返回一个内存表。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个内存表。</returns>
        public static DataTable executeSqlTable(string sql, params object[] args) {
            using (var conn = new SqlConnection(ConfigManager.connectionString)) {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                addParameters(cmd, args);
                var result = new DataTable();
                result.Load(cmd.ExecuteReader());
                return result;
            }
        }

        /// <summary>
        /// 执行数据库语句返回一个内存表。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <returns>一个内存表。</returns>
        public static DataTable executeSqlTable(string sql) {
            return executeSqlTable(sql, null);
        }

        /// <summary>
        /// 执行数据库语句返回一个表示第一行数据的内存数组。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个内存数组。</returns>
        public static object[] executeSqlArray(string sql, params object[] args) {
            using (var conn = new SqlConnection(ConfigManager.connectionString)) {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                addParameters(cmd, args);
                var reader = cmd.ExecuteReader();
                if (!reader.Read()) {
                    return null;
                }
                var result = new object[reader.FieldCount];
                for (var i = 0; i < result.Length; i++) {
                    result[i] = reader[i];
                }
                return result;
            }
        }

        /// <summary>
        /// 执行数据库语句返回一个表示第一行数据的内存数组。
        /// </summary>
        /// <param name="sql">要执行的 SQL 语句。</param>
        /// <returns>一个内存数组。</returns>
        public static object[] executeSqlArray(string sql) {
            return executeSqlArray(sql, null);
        }

        private static void addParameters(SqlCommand cmd, object[] args) {
            if (args == null) {
                return;
            }
            for (int i = 0; i < args.Length; i++) {
                cmd.Parameters.AddWithValue(i.ToString(), args[i] ?? DBNull.Value);
            }
        }

        /// <summary>
        /// 创建一个用于执行命令的空对象。
        /// </summary>
        /// <returns></returns>
        public static SqlCommand createCommand() {
            var conn = new SqlConnection(ConfigManager.connectionString);
            conn.Open();
            return conn.CreateCommand();
        }

        #endregion

        #region 简单 SQL 封装

        /// <summary>
        /// 执行一个简单的 INSERT 语句。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="fields">要插入的字段。格式如“fieldA, fieldB”。</param>
        /// <param name="values">插入的值。格式如“1, '2'”，其值必须和字段一一对应。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>返回受影响的条数。</returns>
        public static int insert(string table, string fields, string values, params object[] args) {
            StringBuilder sb = new StringBuilder(32);
            sb.Append("INSERT INTO");
            sb.Append(table);
            sb.Append("(");
            sb.Append(fields);
            sb.Append(")");
            sb.Append(" VALUES(");
            sb.Append(values);
            sb.Append(")");
            return executeSqlNonQuery(sb.ToString(), args);
        }

        /// <summary>
        /// 执行一个简单的 DELETE 语句。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="where">删除的条件表达式。如果无条件请传递 “1=1”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>返回受影响的条数。</returns>
        public static int delete(string table, string where, params object[] args) {
            StringBuilder sb = new StringBuilder(32);
            sb.Append("DELETE FROM ");
            sb.Append(table);
            sb.Append(" WHERE ");
            sb.Append(where);
            return executeSqlNonQuery(sb.ToString(), args);
        }

        /// <summary>
        /// 执行一个简单的 UPDATE 语句。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="values">要更新的字段值。格式如“fieldA='1', fieldB='2'”</param>
        /// <param name="where">更新的条件表达式。如果无条件请传递 “1=1”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>返回受影响的条数。</returns>
        public static int update(string table, string values, string where, params object[] args) {
            StringBuilder sb = new StringBuilder(32);
            sb.Append("UPDATE ");
            sb.Append(table);
            sb.Append(" SET ");
            sb.Append(values);
            sb.Append(" WHERE ");
            sb.Append(where);
            return executeSqlNonQuery(sb.ToString(), args);
        }

        /// <summary>
        /// 执行一个简单的 SELECT 语句。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="fields">要查询的字段。格式如“fieldA, fieldB”。要查询所有字段请传递“*”。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="orderBy">排序使用的字段。如果不排序请传递 null。如果需要倒序，在字段名后追加“DESC”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static IEnumerable<SqlDataReader> selectRows(string table, string fields = "*", string where = null, string orderBy = null, params object[] args) {
            return executeSqlRows(generateSelect(table, fields, where, orderBy), args);
        }

        /// <summary>
        /// 执行一个简单的 SELECT 语句。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="fields">要查询的字段。格式如“fieldA, fieldB”。要查询所有字段请传递“*”。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="orderBy">排序使用的字段。如果不排序请传递 null。如果需要倒序，在字段名后追加“DESC”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static DataTable selectTable(string table, string fields = "*", string where = null, string orderBy = null, params object[] args) {
            return executeSqlTable(generateSelect(table, fields, where, orderBy), args);
        }

        /// <summary>
        /// 执行一个简单的 SELECT 语句。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="fields">要查询的字段。格式如“fieldA, fieldB”。要查询所有字段请传递“*”。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="orderBy">排序使用的字段。如果不排序请传递 null。如果需要倒序，在字段名后追加“DESC”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static SqlDataReader selectReader(string table, string fields = "*", string where = null, string orderBy = null, params object[] args) {
            return executeSqlReader(generateSelect(table, fields, where, orderBy), args);
        }

        private static string generateSelect(string table, string fields, string where, string orderBy) {
            StringBuilder sb = new StringBuilder(32);
            sb.Append("SELECT ");
            sb.Append(fields);
            sb.Append(" FROM ");
            sb.Append(table);
            if (where != null) {
                sb.Append(" WHERE ");
                sb.Append(where);
            }
            if (orderBy != null) {
                sb.Append(" ORDER BY ");
                sb.Append(orderBy);
            }
            return sb.ToString();
        }

        #endregion

        #region 对象 SQL 封装

        /// <summary>
        /// 在指定的表插入一行数据。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="value">要插入的键值对。</param>
        /// <returns>返回新建行的序号。</returns>
        public static int insert(string table, object value) {
            StringBuilder sb = new StringBuilder(32);
            sb.Append("INSERT INTO ");
            sb.Append(table);
            sb.Append("(");
            var fields = value.GetType().GetProperties();
            var values = new object[fields.Length];
            for (int i = 0; i < fields.Length; i++) {
                if (i > 0) {
                    sb.Append(", ");
                }
                sb.Append("[");
                sb.Append(fields[i].Name);
                sb.Append("]");
                values[i] = fields[i].GetValue(value, null);
            }
            sb.Append(") VALUES(");
            for (var i = 0; i < values.Length; i++) {
                if (i > 0) {
                    sb.Append(", ");
                }
                sb.Append("@");
                sb.Append(i);
            }
            sb.Append(");SELECT @@IDENTITY");
            return (int)(decimal)executeSqlScalar(sb.ToString(), values);
        }

        /// <summary>
        /// 更新指定行。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="where">更新的条件表达式。如果无条件请传递 “1=1”。</param>
        /// <param name="value">要更新的键值对。</param>
        /// <returns>如果更新成功返回 true；否则返回 false。</returns>
        public static int update(string table, string where, object value) {
            var fields = value.GetType().GetProperties();
            StringBuilder sb = new StringBuilder(32);
            sb.Append("UPDATE ");
            sb.Append(table);
            sb.Append(" SET ");
            var values = new object[fields.Length];
            for (int i = 0; i < fields.Length; i++) {
                if (i > 0) {
                    sb.Append(", ");
                }
                sb.Append("[");
                sb.Append(fields[i].Name);
                sb.Append("] = @");
                sb.Append(i);
                values[i] = fields[i].GetValue(value, null);
            }
            sb.Append(" WHERE ");
            sb.Append(where);
            return executeSqlNonQuery(sb.ToString(), values);
        }

        /// <summary>
        /// 查询指定行。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 “1=1”。</param>
        /// <param name="output">要更新的键值对。</param>
        /// <returns>如果查询成功返回 true；否则返回 false。</returns>
        public static bool select(string table, string where, object output) {
            var fields = output.GetType().GetProperties();
            StringBuilder sb = new StringBuilder(32);
            sb.Append("SELECT ");
            for (int i = 0; i < fields.Length; i++) {
                if (i > 0) {
                    sb.Append(", ");
                }
                sb.Append("[");
                sb.Append(fields[i].Name);
                sb.Append("]");
            }
            sb.Append(" FROM ");
            sb.Append(table);
            sb.Append(" WHERE ");
            sb.Append(where);

            using (var reader = executeSqlReader(sb.ToString())) {
                if (!reader.Read()) {
                    return false;
                }

                for (int i = 0; i < fields.Length; i++) {
                    fields[i].SetValue(output, reader[i], null);
                }

                return true;
            }

        }

        /// <summary>
        /// 统计指定表的行数。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>返回行数。</returns>
        public static int count(string table, string where = null, params object[] args) {
            StringBuilder sb = new StringBuilder(32);
            sb.Append("SELECT COUNT(0) FROM ");
            sb.Append(table);
            if (where != null) {
                sb.Append(" WHERE ");
                sb.Append(where);
            }
            return (int)executeSqlScalar(sb.ToString(), args);
        }

        #endregion

        #region 业务相关 SQL 封装

        private const string idFieldName = "[ID]";

        /// <summary>
        /// 删除指定行。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="id">要删除的行的序号。</param>
        /// <returns>如果删除成功返回 true；否则返回 false。</returns>
        public static bool delete(string table, int id) {
            return delete(table, idFieldName + "=" + id.ToString()) > 0;
        }

        /// <summary>
        /// 更新指定行。
        /// </summary>
        /// <param name="table">要更新的表。</param>
        /// <param name="id">要更新的行的序号。</param>
        /// <param name="value">要更新的键值对。</param>
        /// <returns>如果更新成功返回 true；否则返回 false。</returns>
        public static bool update(string table, int id, object value) {
            return update(table, idFieldName + "=" + id.ToString(), value) > 0;
        }

        /// <summary>
        /// 查询指定行。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="id">要查询的行的序号。</param>
        /// <param name="output">要更新的键值对。</param>
        /// <returns>如果查询成功返回 true；否则返回 false。</returns>
        public static bool select(string table, int id, object output) {
            return select(table, idFieldName + "=" + id.ToString(), output);
        }

        private static string generateSelect(string table, int pageNumber, int pageSize, string fields, string where, string orderBy) {
            pageNumber *= pageSize;
            return String.Format("SELECT * FROM( SELECT {0}, ROW_NUMBER() OVER (ORDER BY {2}) AS ct FROM {1}{5}) sub WHERE ct >= {3} AND ct < {4}", fields, table, orderBy ?? idFieldName, pageNumber, pageNumber + pageSize, where != null ? " WHERE " + where : null);
        }

        /// <summary>
        /// 执行一个简单的 SELECT 语句。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="fields">要查询的字段。格式如“fieldA, fieldB”。要查询所有字段请传递“*”。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="orderBy">排序使用的字段。如果不排序请传递 null。如果需要倒序，在字段名后追加“DESC”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static IEnumerable<SqlDataReader> selectRows(string table, int pageNumber, int pageSize = 20, string fields = "*", string where = null, string orderBy = null, params object[] args) {
            return executeSqlRows(generateSelect(table, pageNumber, pageSize, fields, where, orderBy), args);
        }

        /// <summary>
        /// 查询指定表。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="fields">要查询的字段。格式如“fieldA, fieldB”。要查询所有字段请传递“*”。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="orderBy">排序使用的字段。如果不排序请传递 null。如果需要倒序，在字段名后追加“DESC”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static DataTable selectTable(string table, int pageNumber, int pageSize = 20, string fields = "*", string where = null, string orderBy = idFieldName, params object[] args) {
            return executeSqlTable(generateSelect(table, pageNumber, pageSize, fields, where, orderBy), args);
        }

        /// <summary>
        /// 查询指定表。
        /// </summary>
        /// <param name="table">要查询的表。</param>
        /// <param name="fields">要查询的字段。格式如“fieldA, fieldB”。要查询所有字段请传递“*”。</param>
        /// <param name="where">查询的条件表达式。如果无条件请传递 null。</param>
        /// <param name="orderBy">排序使用的字段。如果不排序请传递 null。如果需要倒序，在字段名后追加“DESC”。</param>
        /// <param name="args">SQL 语句中使用的参数值。</param>
        /// <returns>一个用于遍历所有行数据的迭代器。</returns>
        public static SqlDataReader selectReader(string table, int pageNumber, int pageSize = 20, string fields = "*", string where = null, string orderBy = idFieldName, params object[] args) {
            return executeSqlReader(generateSelect(table, pageNumber, pageSize, fields, where, orderBy), args);
        }

        #endregion

    }

}